/*Test Cases Created by Steven Chow
 * This Test Cases tests for populating
 * each database
 * 
 * All Tests are independant from each other.
 */
package com.JUnitTestsDB;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

import com.Helper.AirplaneStatus;
import com.Helper.SeatInfo;
import com.database.AdministratorDB;
import com.database.CustomerDB;
import com.database.FlightDB;
import com.database.ReservationDB;
import com.database.SeatDB;

public class PopulateDBTest {

	private Connection connection;
	private int numOfTuples = 10;

	// Setup Connection for Test
	public PopulateDBTest() {
		String connectionString = "jdbc:mysql://localhost/planesimple?" 												
				+ "user=bluebear&password=bluebeariscute89";
		try {
			// Ensure the SQL Server driver class is available.
			Class.forName("com.mysql.jdbc.Driver");

			// Establish the connection.
			connection = DriverManager.getConnection(connectionString);
		}
		// Exception handling
		catch (ClassNotFoundException cnfe) {

			System.out.println("ClassNotFoundException " + cnfe.getMessage());
		} catch (Exception e) {
			System.out.println("Exception " + e.getMessage());
			e.printStackTrace();
		}
	}

	// Gets the number of tuples in the Database
	private int getTupleCount(String string) {
		int count = 0;
		String sqlString = "SELECT * FROM " + string;
		Statement stmt;
		try {
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlString);
			while (rs.next()) {
				count++;
			}
		} catch (SQLException e) {
			System.out.println("EXCEPTION BABY!");
		}
		return count;
	}

	// Test 1: Populate Admin Database
	@Test
	public void InsertAdmin() {
		// Loop for the amount of times specified by numofTuples
		for (int i = 0; i < numOfTuples; i++) {
			AdministratorDB db = new AdministratorDB(i + 1, "pass" + i, "bob"
					+ i, "douche" + i, "coolguy" + i, 778);
			db.Setup(connection);
			try {
				db.Insert();
			} catch (SQLException e) {
				System.out.println("Exception BABY!");
			}
		}
		// Test if the Database has the same amount of tuples as numOfTuples
		assertEquals(10, getTupleCount("Administrator"));
	}

	// Test 2: Populate Customer Database
	@Test
	public void InsertCustomer() {
		// Loop for the amount of times specified by numofTuples
		for (int i = 0; i < numOfTuples; i++) {
			CustomerDB db = new CustomerDB("User" + (i + 1), "pass" + (i + 1),
					"Blue", "Bear" + (i + 1), "BlueBear@hotmail.com", 999, 1000);
			db.Setup(connection);
			try {
				db.Insert();
				assertEquals(true, db.UserExists("User" + (i + 1)));
			} catch (SQLException e) {
				System.out.println("Exception BABY!");
			}
		}

	}

	@Test
	// Test 3: Populate Flight Database
	public void InsertTheFlights() {

		try {
			//Lets Insert Flight #1
			FlightDB db = new FlightDB("1000", "Air Canada", "Vancouver",
					"2012-10-9 13:00:00", "Los Angeles", "2012-10-9 17:00",
					AirplaneStatus.onTime, 5000f, 122, "A1", 200f);
			db.Setup(connection);
			db.Insert();
			//Lets Insert some seats here
			InsertSeatList("1000");
			
			//Lets Insert Flight #2, our returning Flight
			FlightDB db1 = new FlightDB("1001", "Air Canada", "Los Angeles",
					"2012-10-10 14:00:00", "Vancouver", "2012-10-9 18:00",
					AirplaneStatus.onTime, 5000f, 122, "A1", 200f);
			db1.Setup(connection);
			db1.Insert();
			//Lets Insert some seats here
			InsertSeatList("1001");
			
			//Let's Insert Flight #3
			FlightDB db2 = new FlightDB("1002", "Air Canada", "Williams Lake",
					"2012-12-12 14:00:00", "Vancouver", "2012-12-12 15:00",
					AirplaneStatus.onTime, 550f, 122, "A1", 200f);
			db2.Setup(connection);
			db2.Insert();
			//Let's Insert some seats here
			InsertSeatList("1002");
			//Let's create a Customer that will reserve this flight
			CustomerDB Cdb = new CustomerDB("ReserveUser", "pass",
					"Brown", "Bear", "BrownBear@hotmail.com", 999, 500);
				Cdb.Setup(connection);
				Cdb.Insert();
			//Let's create more customers;
			for(int i=0; i <4; i++)
			{
				CustomerDB Cdb1 = new CustomerDB("ReserveUser"+(i+1), "pass",
					"Blue", "Bear", "BrownBear@hotmail.com", 999, 50);
				Cdb1.Setup(connection);
				Cdb1.Insert();
			}
			
			//Let's make a flight where it will be populated with 122 Reservations
			FlightDB db3 = new FlightDB("1003", "Air Canada", "Vancouver", "2012-12-25 16:00:00", "London", 
					"2012-12-26 11:00:00", AirplaneStatus.onTime, 5000f, 122, "A1", 200f);
			db3.Setup(connection);
			db3.Insert();
			//Make the seats
			InsertSeatList("1003");
			//Let's create a rich customer that will reserve all the seats in the flight
			CustomerDB Cdb1 = new CustomerDB("RichUser", "pass",
					"Rich", "Bastard", "RichBastard@hotmail.com", 9999, 99999999);
			Cdb1.Setup(connection);
			Cdb1.Insert();
			//Let's make 122 Reservations
			PopulateFlight("1003", "RichUser");
			
			//Let's make a connecting Flight - From Vancouver to Edmonton
			FlightDB db4 = new FlightDB("1004", "Air Canada", "Vancouver",
					"2012-12-12 17:00:00", "Edmonton", "2012-12-12 19:00",
					AirplaneStatus.onTime, 1000f, 122, "A1", 200f);
			db4.Setup(connection);
			db4.Insert();
			//Let's Insert some seats here
			InsertSeatList("1004");
			
			} catch (SQLException e) {
			e.printStackTrace();
			}
	}
	
	//Just used to fill the flights with reservations
	private void PopulateFlight(String flightID, String username) throws SQLException
	{
		String s0 = "ABEF";
		String s1 = "ABCDEF";
		
		for (int i = 0; i < s0.length(); i++)
		{
			for (int j = 10; j <= 11; j++)
			{
				String c = s0.charAt(i) + "";
				ReservationDB db = new ReservationDB("Hot", "Girl", 200,0,"Mcdonalds",(c+j),username,flightID);
				db.Setup(connection);
				db.Insert();
			}
		}
		
		for (int i = 0; i < s1.length(); i++)
		{
			for (int j = 12; j <= 18; j++)
			{

				String c = s1.charAt(i) + "";
				ReservationDB db = new ReservationDB("Hot", "Girl", 150,0,"Mcdonalds",(c+j),username,flightID);
				db.Setup(connection);
				db.Insert();
			}
			
			for (int j = 19; j <= 30; j++)
			{

				String c = s1.charAt(i) + "";
				ReservationDB db = new ReservationDB("Hot", "Girl", 100,0,"Mcdonalds",(c+j),username,flightID);
				db.Setup(connection);
				db.Insert();

			}
		}
	}
	
	private void InsertSeatList(String flightID) throws SQLException
	{
		String s0 = "ABEF";
		String s1 = "ABCDEF";
		
		for (int i = 0; i < s0.length(); i++)
		{
			for (int j = 10; j <= 11; j++)
			{
				SeatInfo info = new SeatInfo();
				String c = s0.charAt(i) + "";
				info.seatID = c + j;
				info.flightID = flightID;
				info.status = "empty";
				SeatDB db = new SeatDB(info);
				db.Setup(connection);
				db.Insert();
			}
		}
		
		for (int i = 0; i < s1.length(); i++)
		{
			for (int j = 12; j <= 30; j++)
			{
				SeatInfo info = new SeatInfo();
				String c = s1.charAt(i) + "";
				info.seatID = c + j;
				
				info.flightID = flightID;
				info.status = "empty";
				SeatDB db = new SeatDB(info);
				db.Setup(connection);
				db.Insert();

			}
		}		
	}

}
